﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Twitterizer;
using Twitterizer.Streaming;
using System.Data.SqlClient; // +++++ by Ji +++++

namespace TCSS_590_Team_Project___Visualize_Data_with_WorldWide_Telescope
{
    public partial class Form1 : Form
    {
        WWT_Interface wwt;
        // This should never be hardcoded! Need to move to an obfuscated properties file or something.
        private static OAuthTokens tokens = new Twitterizer.OAuthTokens
        {
            /* Dan's API Info */
            // Type Ctrl+E+(C|U) to comment uncomment code.
            ConsumerKey = @"TFFBLnE35TrHILtrH8wQtQ",
            ConsumerSecret = @"cU925O6BSTsB9r1M0oTelra2DXrPPAZlTg9V3EieLjE",
            AccessToken = @"872808954-qdGXOUlGI6KlERDy9d8hLApUl7jG515a0SB88Xmp",
            AccessTokenSecret = @"ke2wP0XDBtG1YJFMt5aw44OKfkYegzBRhmEtPBn4"

            /*
             * Rob's API Info
             */

            //ConsumerKey = @"WE3h7yrbvqs5yfb5z09wrw",
            //ConsumerSecret = @"NPvhF3fUMmyuCElBeAcFHindxCby9LAmoHRNOvPRdx0",
            //AccessToken = @"196902335-NkaTgr5znzUEBKiugBTv0bErWmmBCSnLf4rP2Iee",
            //AccessTokenSecret = @"nflATXvmxX72O6MdKLX6goatfIwy2pDb6TxlsZmd8"

        };

        static bool jsonView = false;
        // Space needle lat/lon
        static float space_needle_lat = 47.619920f;
        static float space_needle_lon = -122.348725f;
        static double default_radius = 5; // in mi
        static string sample_keyword = " ";
        bool streamInitialized = false;
        bool isStreaming = false;

        delegate void SetTextCallback(string text, string imagePath);

        public Form1()
        {
            InitializeComponent();

            connectDatabase();

            /*
             * This code is run when the Form is displayed on the screen for the first time.
             * Here are the high level steps we will want to accomplish:
             * 
             * 1. Reach out to Twitter and grab all tweets within a certain date and location range.
             *      This will be accomplished in the method 'getTweets()'.
             *      Daniel and Christian have already begun/completed the logic for this in the project 'PullFromTwitter'
             *  
             * 2. Store the retrieved Tweets in SQL
             *      This will be accomplished in the method 'storeTweets()'
             *      Ji has built the SQL database, we just need to install it on our machines
             *      We will also need to determine who will write the code that does the SQL insert
             *      
             * 3. Send tweets to WorldWideTelescope
             *      This will be accomplished in the method 'displayTweets()'
             * 
             */

            this.Location = new Point(SystemInformation.WorkingArea.Width - this.Width, this.Location.Y);

            wwt = new WWT_Interface();
            progressBar1.ProgressBar.Visible = false;
            listBox1.ResetText();

            updateStatus("Streaming is paused.  Click the Start button.");
            /*
            myDateTime start = new myDateTime(2012, 11, 30, 0, 0, 0);
            myDateTime end = new myDateTime(2012, 11, 30, 23, 55, 0);
            queryTweets(start, end);
            */

            //wwt.DisplayWWTProperties();
        }

        private void updateStatus(string status)
        {
            updateStatus(status, null);
        }

        private void updateStatus(string status, string imagePath)
        {
            if (this.listBox1.InvokeRequired)
            {
                SetTextCallback d = new SetTextCallback(updateListBox);
                this.Invoke(d, new object[] { status, imagePath });
            }
            else
            {
                updateListBox(status, imagePath);
            }

        }

        private void updateListBox(string status, string imagePath)
        {
            listBox1.Items.Insert(0, status);
            listBox1.Refresh();

            Console.WriteLine(status);
        }

        private void startButton_Click(object sender, EventArgs e)
        {
            if (!streamInitialized)
                initializeTweetStream();

            progressBar1.Visible = true;
            progressBar1.Value = progressBar1.Maximum / 2;
            isStreaming = true;
        }

        /// <summary>
        /// This represents Step 1 in our process.
        /// 
        /// This logic will reach out to Twitter and follow all users that are friends of a single predefined user.
        /// This means that we will follow all friends, plus ourselves. Any tweets made by these accounts will get
        /// forwarded to our system for processing via the NewTweet callback method.
        /// 
        /// </summary>
        private void initializeTweetStream()
        {
            int nebby_id = 872808954;
            int robby_id = 196902335;

            int currentTwitterID = nebby_id;

            List<String> follow_names = new List<String>();
            List<Decimal> follow_ids = new List<Decimal>();

            /*/
            TwitterUserCollection coll = Twitterizer.TwitterUser.Lookup(tokens, new LookupUsersOptions() { ScreenNames = { "KingNebby" } }).ResponseObject;
            Console.WriteLine("Found " + coll.Count + " users by that name");
            // Get Friends from single User
            UserIdCollection users = TwitterFriendship.FriendsIds(tokens, new UsersIdsOptions() { UserId = currentTwitterID }).ResponseObject;
            if (users == null)
            {
                updateStatus("Could not find friends ids based on given id.");
                return;
            }
            // Add friend info
            foreach (var status in users)
            {
                //follow_ids.Add(status);
            }
            follow_ids.Add(currentTwitterID);
            // Find Friends
            TwitterUserCollection uses = TwitterUser.Lookup(tokens, new LookupUsersOptions() { UserIds = new TwitterIdCollection(follow_ids) }).ResponseObject;
            if (uses == null)
            {
                updateStatus("Could not find users");
                return;
            }
            // Add Friends for Streaming
            foreach (var use in uses)
            {
                //follow_names.Add(use.ScreenName);
            }
            //follow_names.Add("robbyoesch");*/
            follow_names.Add("@KingNebby");

            updateStatus("Collected " + follow_names.Count + " users to follow");

            if (follow_names.Count > 0)
            {
                StreamOptions options = new StreamOptions();
                // Cannot getting ids working. Will simply track names for now because it works just as well.
                options.Track = follow_names;
                //List<string> ids = new List<string>();
                //ids.Add(follow_ids[0].ToString());
                //options.Follow = ids;

                TwitterStream stream = new TwitterStream(tokens, "Visuallizing Events on WWT 0.9", options);
                stream.StartUserStream(null,
                    null,
                    NewTweet,
                    null,
                    null,
                    null,
                    null);
                updateStatus("Twitter stream started, let the tweeting begin..");
            }
            else
            {
                updateStatus("Did not have names to follow");
            }
        }

        /**
         * This will search twitter with a location and a radius and keyword.
         * We don't want to abuse the API for now.
         */
        public TwitterSearchResultCollection getTweetsForLocationAndRadius(double lat, double lon, double radius, string keyword)
        {
            TwitterSearchResultCollection results = new TwitterSearchResultCollection();
            TwitterResponse<TwitterSearchResultCollection> search = Twitterizer.TwitterSearch.Search(tokens, keyword, new Twitterizer.SearchOptions
            {
                GeoCode = lat + "," + lon + "," + radius + "mi",
                SinceDate = DateTime.Now.AddMinutes(-30),
                NumberPerPage = 100
            });
            if (search.Result != RequestResult.Success)
            {
                updateStatus("Error in getTweetsForLocationAndRadius - " + search.ErrorMessage);
                return null;
            }
            results = search.ResponseObject;
            return results;
        }

        /*
         * This is the main method of interest. This will handle all incoming tweets from the accounts that we have been
         * hooked up to. This needs to go find tweets within the radius of the new tweet searching for key words. It will then
         * push the new tweet and all the found tweets to the DB and GUI.
         * 
         */
        void NewTweet(TwitterStatus tweet)
        {
            if (!isStreaming)
                return;

            float lat = 0;
            float lon = 0;
            string loc = "Not set";
            if (tweet.Geo != null && tweet.Geo.Coordinates[0].Latitude != 0)
            {
                lat = (float)tweet.Geo.Coordinates[0].Latitude;
                lon = (float)tweet.Geo.Coordinates[0].Longitude;
            }
            else
            {
                updateStatus("Could not find lat/lon using default of space needle.");
                lat = space_needle_lat;
                lon = space_needle_lon;
            }
            if (tweet.Place != null && tweet.Place.FullName != null)
            {
                loc = tweet.Place.FullName;
            }
            updateStatus("Tweet:\n  \"" + tweet.Text + "\"\nTime:\n  " + tweet.CreatedDate.ToLocalTime() + " LT\nCoordinates:\n  (" + lat + "," + lon + ")" + "\nPlace:\n  \"" + loc + "\"");
            List<Tweet> our_tweet = new List<Tweet>();
            our_tweet.Add(new Tweet(tweet.Id, lat, lon, tweet.CreatedDate, tweet.Text, true));
            displayTweets(our_tweet, true);
            storeTweets(our_tweet);

            List<Tweet> other_tweets = new List<Tweet>();
            TwitterSearchResultCollection relevantTweets = getTweetsForLocationAndRadius(lat, lon, default_radius, sample_keyword);
            foreach (var rel in relevantTweets)
            {
                if (rel.FromUserId == tweet.User.Id)
                {
                    Console.WriteLine("Skipping tweet: " + rel.Text);
                    continue;
                }
                if (rel.Geo != null && rel.Geo.Coordinates[0].Latitude != 0)
                {
                    float oLat = (float)rel.Geo.Coordinates[0].Latitude;
                    float oLon = (float)rel.Geo.Coordinates[0].Longitude;
                    updateStatus("Found relevant tweet: " + rel.Text);
                    other_tweets.Add(new Tweet(rel.Id, oLat, oLon, rel.CreatedDate, rel.Text, false));
                }
            }
            if (other_tweets.Count > 0)
            {
                displayTweets(other_tweets, false);
                storeTweets(other_tweets);
            }
            else
            {
                updateStatus("Cound not find any tweets with keyword: " + sample_keyword + " actual tweet is: " + our_tweet[0].text_content);
            }
        }

        /// <summary>
        /// This represents Step 2 in our process
        /// 
        /// A collection of tweets will be passed to this method.
        /// </summary>
        /// <param name="tweets"></param>

        // ++++++++++ by Ji ++++++++++
        // connect the datebase at every time the program starts
        private SqlConnection con = null;
        private string connection = "server=localhost\\SQLEXPRESS;Initial Catalog=Ji;Integrated Security=SSPI";

        private void connectDatabase()
        {
            con = new SqlConnection(connection);

            try
            {
                {
                    con.Open();
                    System.Console.WriteLine("ServerVersion:{0}", con.ServerVersion);
                    System.Console.WriteLine("State:{0}", con.State);
                }
            }
            catch (System.Exception sqle)
            {
                System.Console.WriteLine(sqle.Message);
            }
        }

        // store tweets into the database
        private void storeTweets(List<Tweet> tweets)
        {
            /*
             * Insert Logic Here
             *  
             */
            if (con == null)
            {
                updateStatus("Lost connection to database. No data will be stored.");
                return;
            }
            else
            {
                string insert = "EXEC InsertTable @id, @lat, @lon, @time, @info, @isPDTwt";
                //SqlCommand insertTable = new SqlCommand(insert, con);
                //SqlDataReader sdr = null;

                try
                {
                    foreach (Tweet tw in tweets)
                    {
                        SqlCommand insertTable = new SqlCommand(insert, con);
                        SqlDataReader sdr = null;

                        insertTable.Parameters.Add("id", SqlDbType.Decimal).Value = tw.uniq_id;
                        insertTable.Parameters.Add("lat", SqlDbType.Float).Value = tw.latitude;
                        insertTable.Parameters.Add("lon", SqlDbType.Float).Value = tw.longitude;
                        insertTable.Parameters.Add("time", SqlDbType.DateTime).Value = tw.orig_time;
                        insertTable.Parameters.Add("info", SqlDbType.Text).Value = tw.text_content;
                        insertTable.Parameters.Add("isPDTwt", SqlDbType.Bit).Value = tw.isPDTweet;

                        sdr = insertTable.ExecuteReader();
                        sdr.Close();
                    }
                }
                catch (System.Exception sqle)
                {
                    System.Console.WriteLine("Error when storing tweets: " + sqle.Message);
                }
            }
        }

        // make queries to database
        private void queryTweets(DateTime start, DateTime end)
        {
            List<Tweet> fromDB = new List<Tweet>();

            if (con == null)
            {
                updateStatus("Lost connection to database. No query will be made.");
                return;
            }
            else
            {
                string select = "EXEC SelectByCondition @startTime, @endTime";
                SqlCommand selectTable = new SqlCommand(select, con);
                SqlDataReader sdr = null;

                try
                {
                    selectTable.Parameters.Add("startTime", SqlDbType.DateTime).Value = start;
                    selectTable.Parameters.Add("endTime", SqlDbType.DateTime).Value = end;

                    sdr = selectTable.ExecuteReader();
                    if (sdr.Read() == false)
                        updateStatus("No record during this time.");

                    while (sdr.Read())
                    {
                        updateStatus(sdr["TweetID"] + " " + sdr["DatetimeStamp"] + " " + sdr["Latitude"] + " " + sdr["Longitude"] + " " + sdr["TweetContent"] + " " + sdr["PoliceOrNot"]);
                        decimal id = Convert.ToDecimal(sdr["TweetID"]);
                        double lat = Convert.ToDouble(sdr["Latitude"]);
                        double lon = Convert.ToDouble(sdr["Longitude"]);
                        DateTime dt = Convert.ToDateTime(sdr["DatetimeStamp"]);
                        string info = Convert.ToString(sdr["TweetContent"]);
                        bool pdOrNot = Convert.ToBoolean(sdr["PoliceOrNot"]);
                        fromDB.Add(new Tweet(id, (float)lat, (float)lon, dt, info, pdOrNot));
                        //fromDB.Add(new Tweet((decimal)sdr["TweetID"], (float)sdr["Latitude"], (float)sdr["Longitude"], (DateTime)sdr["DatetimeStamp"], (string)sdr["TweetContent"], (bool)sdr["PoliceOrNot"]));
                    }
                    displayTweets(fromDB, false);
                }
                catch (System.Exception sqle)
                {
                    System.Console.WriteLine("Error when storing tweets:" + sqle.Message);
                }
                sdr.Close();

                //displayTweets(fromDB);
            }
        }

        // a structure wrapping the time stamp data
        private class myDateTime
        {
            public int year;
            public int month;
            public int day;
            public int hour;
            public int minute;
            public int second;

            public myDateTime(int y, int mon, int d, int h, int min, int s)
            {
                year = y;
                month = mon;
                day = d;
                hour = h;
                minute = min;
                second = s;
            }
        }
        // ++++++++++ by Ji ++++++++++

        private void displayTweets(List<Tweet> tweets, Boolean centerOnTweet)
        {

            wwt.SendDataToWWT(tweets, centerOnTweet);


            #region TestCode
            /*
            wwt = new WWT_Interface();

            DataGridViewRow row = new DataGridViewRow();
            row.CreateCells(dataGridTweets);
            row.Cells[0].Value = 0;
            row.Cells[1].Value = "2009/01/09 03:44:38.8000";
            row.Cells[2].Value = "10.437";
            row.Cells[3].Value = "56.992";
            row.Cells[4].Value = "Mesage 1";

            dataGridTweets.Rows.Add(row);


            row = new DataGridViewRow();
            row.CreateCells(dataGridTweets);
            row.Cells[0].Value = 1;
            row.Cells[1].Value = "2009/01/09 06:28:35.1000";
            row.Cells[2].Value = "-19.287";
            row.Cells[3].Value = "-66.595";
            row.Cells[4].Value = "Mesage 2";

            dataGridTweets.Rows.Add(row);


            row = new DataGridViewRow();
            row.CreateCells(dataGridTweets);
            row.Cells[0].Value = 2;
            row.Cells[1].Value = "2009/01/09 06:54:47.7000";
            row.Cells[2].Value = "-2.231";
            row.Cells[3].Value = "99.774";
            row.Cells[4].Value = "Mesage 3";

            dataGridTweets.Rows.Add(row);

            row = new DataGridViewRow();
            row.CreateCells(dataGridTweets);
            row.Cells[0].Value = 3;
            row.Cells[1].Value = "2009/01/09 07:28:45.7000";
            row.Cells[2].Value = "16.064";
            row.Cells[3].Value = "119.766";
            row.Cells[4].Value = "Mesage 4";

            dataGridTweets.Rows.Add(row);

            row = new DataGridViewRow();
            row.CreateCells(dataGridTweets);
            row.Cells[0].Value = 4;
            row.Cells[1].Value = "2012/10/01 07:28:45.7000";
            row.Cells[2].Value = "47.62065";
            row.Cells[3].Value = "-122.34874";
            row.Cells[4].Value = "I just went to the top of the Space Needle";

            dataGridTweets.Rows.Add(row);
            */
            #endregion
        }

        public class Tweet
        {
            public decimal uniq_id;
            public float latitude;
            public float longitude;
            public DateTime orig_time;
            public string text_content;
            public bool isPDTweet;

            public Tweet()
            {
            }

            public Tweet(decimal id, float lat, float lon, DateTime orig_time, string info, bool isPDTwt)
            {
                uniq_id = id;
                latitude = lat;
                longitude = lon;
                this.orig_time = orig_time;
                text_content = info;
                isPDTweet = isPDTwt;
            }

            override public String ToString()
            {
                return "Tweet tweet!";
            }
        }

        private void pauseButton_Click(object sender, EventArgs e)
        {
            isStreaming = false;
            progressBar1.Visible = false;
        }

        // close the connection to database when closing the main window
        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            try
            {
                {
                    con.Close();
                    System.Console.WriteLine("State:{0}", con.State);
                }
            }
            catch (System.Exception sqle)
            {
                System.Console.WriteLine(sqle.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            updateStatus("Query from " + dateTimePicker1.Value + " to " + dateTimePicker2.Value);
            //updateStatus("Time: " + dateTimePicker2.Value);
            queryTweets(dateTimePicker1.Value, dateTimePicker2.Value);
        }


    }
}
